package com.itsitio.registeradmin.dao.impl;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.itsitio.registeradmin.dao.UserRegistrationDao;
import com.itsitio.registeradmin.dao.base.SpringHibernateDao;
import com.itsitio.registeradmin.dto.UserRegistrationDTO;
import com.itsitio.registeradmin.dto.UserRegistrationStatisticDTO;
import com.itsitio.registeradmin.model.UserRegistration;
import com.itsitio.registeradmin.web.controller.PortalController;

@Repository
public class UserRegistrationDaoImpl extends SpringHibernateDao<UserRegistration> implements
UserRegistrationDao{
	
	private static final Logger log = LoggerFactory.getLogger(UserRegistrationDaoImpl.class);
	
	@Autowired
	public UserRegistrationDaoImpl(SessionFactory sessionFactory) {
		super(sessionFactory);
	}
	
	@Override
	protected Class<UserRegistration> getObjetoManejado() {
		return UserRegistration.class;
	}

	@Transactional(readOnly = true)
	public List<UserRegistration> findUserRegistrationReq(Integer portalId, Integer countryId){

		if(log.isDebugEnabled())
			log.debug("Begin with portalId="+portalId+", countryId="+countryId);
		
		DetachedCriteria criteria = DetachedCriteria.forClass(UserRegistration.class);
		//criteria.add(Restrictions.like("userEmail", "@", MatchMode.ANYWHERE));
		criteria.add(Restrictions.eq("portal.id", portalId));
		criteria.createAlias("portal.country", "countryAlias").add(Restrictions.eq("countryAlias.id", countryId));
		//criteria.add(Restrictions.like("companyFirmName", "t", MatchMode.START));
		
		List<UserRegistration> result = getHibernateTemplate().findByCriteria(criteria);
		if (log.isDebugEnabled()) {
			if (result != null)
				log.debug("UserRegistration result.size()=" + result.size());
			else
				log.debug("UserRegistration result=null");
		}
			
		return result;
		//return getHibernateTemplate().findByCriteria(criteria);		
		//return getHibernateTemplate().loadAll(getObjetoManejado());
			
	}
	
	public List<UserRegistrationDTO> findUserRegistrationReq(Integer portalId, Integer countryId, Date fromDate, 
			Date toDate, String firstName, String lastName, String email, String state){

		if(log.isDebugEnabled())
			log.debug("Begin with portalId="+portalId+", countryId="+countryId+", fromDate="+fromDate+", toDate="+toDate);
		
		/*
		DetachedCriteria criteria = DetachedCriteria.forClass(UserRegistration.class);
		//criteria.add(Restrictions.like("userEmail", "@", MatchMode.ANYWHERE));
		criteria.add(Restrictions.eq("portal.id", portalId));
		criteria.createCriteria("portal.countries", "countryAlias").add(Restrictions.eq("countryAlias.id", countryId));
		if (fromDate != null && toDate != null){
			criteria.add(Restrictions.between("registrationDate", fromDate, toDate));
		}
		if (firstName != null && firstName.trim().length() > 0){
			criteria.add(Restrictions.like("userFirstName", firstName, MatchMode.ANYWHERE));
		}
		if (lastName != null && lastName.trim().length() > 0){
			criteria.add(Restrictions.like("lastName", lastName, MatchMode.ANYWHERE));
		}
		if (email != null && email.trim().length() > 0){
			criteria.add(Restrictions.like("userEmail", email, MatchMode.ANYWHERE));
		}
		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

		//criteria.add(Restrictions.like("companyFirmName", "t", MatchMode.START));
		
		List<UserRegistration> result = getHibernateTemplate().findByCriteria(criteria);
		*/
		SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		String fromDateString = dateFormatter.format(fromDate);
        String foDateString = dateFormatter.format(toDate);
        
		
		StringBuilder selectSqlQuery = new StringBuilder()
		.append("Select ur.idx as id, pai.descripcion as countryName, por.descripcion as portalName, ur.fecha as registrationDate, ") 
		.append("ur.usuario_nombre as userFirstName, ur.usuario_apellido as userLastName, ur.usuario_email as userEmail, ur.empresa_fantasia as companyTradeName, ")
		.append("ur.usuario_domicilio as userAddress, ur.usuario_localidad as userCity, ur.usuario_telefono as userPhone, ")
		.append("ur.estado as status, ur.observaciones as comments ")
		.append("From paises as pai,  portales as por, view_portalesXpaises as vpxp, usuarios_registracion as ur ");
		
		StringBuilder whereSqlQuery = new StringBuilder()
		.append("Where vpxp.idxPais=pai.idx and vpxp.idxPortal=por.idx and por.status=1 and pai.idx="+countryId+" and por.idx="+portalId+" and por.idx=ur.carac_idxportal and pai.idx=ur.carac_idxpais ")
		.append("and ur.fecha between '"+fromDateString+"' and '"+foDateString+"' ");
		
		if (firstName != null && firstName.trim().length() > 0){
			whereSqlQuery.append(" and ur.usuario_nombre like ('%"+firstName+"%')");
		}
		
		if (lastName != null && lastName.trim().length() > 0){
			whereSqlQuery.append(" and ur.usuario_apellido like ('%"+lastName+"%')");
		}

		if (email != null && email.trim().length() > 0){
			whereSqlQuery.append(" and ur.usuario_email like ('%"+email+"%')");
		}		
		
		if (state != null && state.trim().length() > 0){
			whereSqlQuery.append(" and ur.estado='"+state+"'"); 			
		}

		StringBuilder orderBySqlQuery = new StringBuilder();
		orderBySqlQuery.append(" Order by ur.fecha");

		selectSqlQuery.append(whereSqlQuery);
		selectSqlQuery.append(orderBySqlQuery);

		if(log.isDebugEnabled())
			log.debug("Before execute selectSqlQuery="+selectSqlQuery.toString());

		//, ur.observaciones as comments
		//.addScalar("comments", Hibernate.STRING)
		List<UserRegistrationDTO> result = getSession().createSQLQuery(selectSqlQuery.toString())
				.addScalar("id")
				.addScalar("countryName")
				.addScalar("portalName")
				.addScalar("registrationDate")
				.addScalar("userFirstName")
				.addScalar("userLastName")
				.addScalar("userEmail")
				.addScalar("companyTradeName")
				.addScalar("userAddress")
				.addScalar("userCity")
				.addScalar("userPhone")
				.addScalar("status", Hibernate.STRING)
				.addScalar("comments", Hibernate.STRING)
				
				.setResultTransformer(Transformers.aliasToBean(UserRegistrationDTO.class))
				.list();
		

		if (log.isDebugEnabled()) {
			if (result != null)
				log.debug("UserRegistration result.size()=" + result.size());
			else
				log.debug("UserRegistration result=null");
		}
			
		return result;
		//return getHibernateTemplate().findByCriteria(criteria);		
		//return getHibernateTemplate().loadAll(getObjetoManejado());
			
	}
	
	public List<UserRegistrationStatisticDTO> findUserRegistrationStatistic(Integer userId){

		if(log.isDebugEnabled())
			log.debug("Begin with userId="+userId);
	
		//TODO este sql query debe ser modificado para tener en cuenta los
		//TODO permisos del usuario que esta logueado en el site
		/*
		StringBuilder sqlQuery = new StringBuilder()
		.append("select ver.idxpais as countryId, ver.pais_nombre as countryName, ver.idxportal as portalId, ver.portal_nombre as portalName, ver.nuevas as neww, ")
		.append("ver.modificadas as changes, ver.pendientes as pending, ver.aceptadas as accepted, ")
		.append("ver.rechazadas as rejected, ver.eliminadas as dropped from view_estadisticas_registraciones as ver ")
		.append("where idxpais in (0, 222, 232, 233, 237) ")
		.append("and idxportal in (41, 55, 56, 60, 61, 68, 69, 73, 75, 84, 148, 151, 154, 155, 157, 160, 165, 170, 171, 176, 178, 181, 195, 196) ");
		*/
		StringBuilder sqlQuery = new StringBuilder()
		.append("select ")
		.append("regisPaisPortCantidad.idxpais as countryId, regisPaisPortCantidad.pais_nombre as countryName, regisPaisPortCantidad.idxportalID as portalId, regisPaisPortCantidad.portal_nombre as portalName, ")
		.append("MAX(IF(estado = 'nueva', cantidad, 0)) as neww, ")
		.append("MAX(IF(estado = 'modificacion', cantidad, 0)) as changes, ")
		.append("MAX(IF(estado = 'pendiente', cantidad, 0)) as pending, ")
		.append("MAX(IF(estado = 'aceptada', cantidad, 0)) as accepted, ")
		.append("MAX(IF(estado = 'rechazada', cantidad, 0)) as rejected, ")
		.append("MAX(IF(estado = 'baja', cantidad, 0)) as dropped ")
		.append("from ( ")
		//.append("select pai.idx as idxpais, pai.descripcion as pais_nombre, por.idx as idxportalID, por.descripcion as portal_nombre, ur.estado, count(*) as cantidad ")
		//.append("from  portalesXcomunidadesXpaises as pxp, paises as pai,  portales as por, usuarios_registracion as ur, rol_usuarios as ru ")
		//.append("where ")
		//.append("ru.idxusuario = 2000232959 and ru.idxrol = 38 and ")
		//.append("ru.tiporol = pxp.IdxPortal  and ru.idxpais = pxp.IdxPais and pxp.IdxPais=pai.idx and pxp.IdxPortal=por.idx and por.status=1 and por.idx=ur.carac_idxportal ")
		//.append("group by idxpais, idxportal, ur.estado ")
		
		.append(" select pai.idx as idxpais, pai.descripcion as pais_nombre, por.idx as idxportalID, por.descripcion as portal_nombre, ur.estado, count(*) as cantidad ")
		.append(" from  view_portalesXpaises as vpxp, paises as pai,  portales as por, usuarios_registracion as ur, rol_usuarios as ru ")
		.append(" where ")
		.append(" ru.idxusuario = "+userId+" and ru.idxrol = 38 and ")
		.append(" ru.tiporol = vpxp.IdxPortal  and ru.idxpais = vpxp.idxPais and vpxp.idxPais=pai.idx and vpxp.idxPortal=por.idx and por.status=1 ") 
		.append(" and por.idx=ur.carac_idxportal ")
		.append(" and pai.idx=ur.carac_idxpais ")
		.append(" group by  idxpais, idxportal, ur.estado ")
	
		//.append("select pai.idx as idxpais, pai.descripcion as pais_nombre, por.idx as idxportalID, por.descripcion as portal_nombre, ur.estado, count(*) as cantidad ")
		//.append("from  portalesXcomunidadesXpaises as pxp, paises as pai,  portales as por, usuarios_registracion as ur ")
		//.append("where pxp.IdxPortal in (select distinct ru1.tiporol as paisID from rol_usuarios as ru1 where ru1.idxusuario = 2000232959 and ru1.idxrol = 38) ") 
		//.append("and pxp.IdxPais in (select distinct ru2.idxpais from rol_usuarios as ru2 where ru2.idxusuario = 2000232959 and ru2.idxrol = 38) and pxp.IdxPais=pai.idx and pxp.IdxPortal=por.idx and por.status=1 and por.idx=ur.carac_idxportal ")
		//.append("group by idxpais, idxportal, ur.estado ")		
		.append(")as regisPaisPortCantidad ")
		.append("group by ")
		.append("regisPaisPortCantidad.idxpais, regisPaisPortCantidad.pais_nombre, regisPaisPortCantidad.idxportalID, regisPaisPortCantidad.portal_nombre ");
	
		List<UserRegistrationStatisticDTO> userRegistrationStatisticList = getSession().createSQLQuery(sqlQuery.toString())
				.addScalar("countryId")
				.addScalar("countryName")
				.addScalar("portalId")
				.addScalar("portalName")
				.addScalar("neww", Hibernate.LONG)
				.addScalar("changes", Hibernate.LONG)
				.addScalar("pending", Hibernate.LONG)
				.addScalar("accepted", Hibernate.LONG)
				.addScalar("rejected", Hibernate.LONG)
				.addScalar("dropped", Hibernate.LONG)
				.setResultTransformer(Transformers.aliasToBean(UserRegistrationStatisticDTO.class))
				.list();
		
			

		/*
		private String neww;
		private String changes;
		private String pending;
		private String accepted;
		private String rejected;
		private String dropped;	
		
		.addScalar("idxpais")
		.addScalar("pais_nombre")
		.addScalar("idxportal")
		.addScalar("portal_nombre")
		.addScalar("nuevas")
		.addScalar("modificadas")
		.addScalar("pendientes")
		.addScalar("aceptadas")
		.addScalar("rechazadas")
		.addScalar("eliminadas")
		*/
		//UserRegistrationStatisticDTO dto = (UserRegistrationStatisticDTO) userRegistrationStatisticList.get(0);
		
		return userRegistrationStatisticList;
	}
	

	
}
